package pms;

import java.io.IOException;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Vector;

import org.joda.time.DateMidnight;
import org.joda.time.DateTime;
import org.joda.time.Duration;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;

public class Supervisor extends Employee{
	
	public Supervisor(int employeeID) throws Exception
	{
		
		super(employeeID);
		
		// TODO Auto-generated constructor stub
	}
	
	public Supervisor(String username) throws Exception
	{
		super(username);
		
		// TODO Auto-generated constructor stub
	}

	public Supervisor(String username, String password) throws Exception {
		// TODO Auto-generated constructor stub
		super(username, password);
	}

	public int managerID;
	
	public  void updateEmployeeInfo(String firstName, String lastName,String address,int jobID,
			double payRate, int PTO, DateTime startDate,int supervisorID, String empID){
		String employeeInfo= "first_name ='"+firstName+"', last_name ='"+lastName+"', address ='"+address+"', jobID ="+jobID+",payRate ="
				+payRate+", PTO= "+PTO+",'supervisorID"+supervisorID+"";
		String columns ="first_name,last_name,address,jobID,payRate,PTO,startDate,supervisorID";
		db.update("employeeInfo",employeeInfo,empID +" = employeeID");
	}
	public void changePassword(int empID, String pass){
		db.update("employeeInfo"," password ='"+pass+"'",empID +" = employeeID");
	}
	public void changeUsername(int empID, String user){
		db.update("employeeInfo"," username ='"+user+"'",empID +" = employeeID");
	}
	
	public void approveTimeOff(int alertNumber) throws Exception
	{
		ResultSet rs= db.select("startDate,endDate,employeeID", "alerts", "alertNumber ="+alertNumber);
		rs.next();
		int empID=rs.getInt("employeeID");
		ResultSet r = db.select("PTO", "employeeInfo", "employeeID = " + empID);
		DateTime d1= new DateTime();
		DateTimeFormatter df = DateTimeFormat.forPattern("yyyy-MM-dd '00:00:00.0'");
		String sd = rs.getString("startDate");
		d1 = DateTime.parse(sd, df);
		DateTime d2= new DateTime();
		String ed = rs.getString("endDate");
		d2 = DateTime.parse(ed, df);
		r.next();
		int PTO = r.getInt("PTO");
//		System.out.println("d1: " + d1.toString() + " d2: " + d2.toString());
		
		Duration d = new Duration(d1, d2);
//		System.out.println("dur: " + d.getStandardDays());
		int hours = (int) (d.getStandardDays() * 8);
		DateTime today = new DateTime();
		DateTimeFormatter dft = DateTimeFormat.forPattern("yyyy/MM/dd HH:mm:ss");
		String date = today.toString(dft);
		String vacDate = d1.toString(dft);
		db.insert("hours","dateEntered,hoursWorked,startTime,endTime,employeeID", "'"+date+"' , " + hours + " , '" + vacDate+ "' , '"+vacDate+"' , " + empID );
		db.delete("alerts", "alertNumber = " + alertNumber);
		minusPTO(empID, PTO - d.getStandardDays());
	}
	
	public void editEmployeeHours(int entry, double numHours){
		DateTimeFormatter dft = DateTimeFormat.forPattern("yyyy/MM/dd HH:mm:ss");
		db.update("hours","hoursWorked = "+numHours," entryNumber = " + entry);
	}

	public Vector<Alert> getAlerts() throws Exception{
		ResultSet rs = db.select("*","alerts","supervisorID = "+employeeID);
		Vector<Alert> c = new Vector<Alert>();
		while(rs.next()){
			DateTime r = new DateTime(); //yyyy/MM/dd HH:mm:ss
			DateTimeFormatter formatter = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss.0");
			r = formatter.parseDateTime(rs.getString("requestDate"));
			DateTime s = new DateTime();
			s = formatter.parseDateTime(rs.getString("startDate"));
			DateTime e = new DateTime();
			e = formatter.parseDateTime(rs.getString("endDate"));
			DateMidnight s2 = s.toDateMidnight();
			DateMidnight e2 = e.toDateMidnight();
			int empID = rs.getInt("employeeID");
			ResultSet rs2 = db.select("first_name, last_name, PTO","employeeInfo","employeeID ="+empID);
			String firstname = "";
			String lastname = "";
			
			if (rs2.next()){
				firstname = rs2.getString("first_name");
				lastname = rs2.getString("last_name");
			}
			Alert a = new Alert(rs.getInt("alertNumber"), rs.getString("description"), r, s2, e2, empID, firstname, lastname, rs.getInt("supervisorID"));
			c.add(a);
		}
		
		return c;
	}
	
	public void minusPTO (int employeeID, long l) {
		db.update("employeeInfo", "PTO = " + l , "employeeID = " + employeeID);
	}
	
	public Collection<Integer> getMinions() throws Exception {
		ResultSet r = db.select("employeeID", "employeeInfo", "last_name", "ASC");
		
		ArrayList<Integer> eids = new ArrayList<Integer>();
		
		while(r.next())
			eids.add(r.getInt("employeeID"));
		
		return eids;
	}
	
	public Vector<Log> getHours (int employeeID, DateTime start, DateTime end) throws Exception {
		DateTimeFormatter dft = DateTimeFormat.forPattern("yyyy/MM/dd");
//		System.out.println("We are about to select.");
		ResultSet r = db.select("*", "hours", "DATE(startTime) BETWEEN '" + start.toString(dft) + "' AND '" + end.toString(dft) +"' AND employeeID = " + employeeID);
		
		
		DateTimeFormatter formatter = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss.0");
		Vector<Log> eHours = new Vector<Log>();
		while(r.next())
		{
			Log temp = new Log();
			String t = r.getString("dateEntered");
//			System.out.println("t = " + t);
			temp.dateEntered = formatter.parseDateTime(t);
			temp.employeeID = r.getInt("employeeID");
			temp.endTime = formatter.parseDateTime(r.getString("endTime"));
			temp.entryNumber = r.getInt("entryNumber");
			temp.hoursWorked = r.getDouble("hoursWorked");
			temp.startTime = formatter.parseDateTime(r.getString("endTime"));
			eHours.add(temp);
		}
			
		
		return eHours;
	}

	public ArrayList<Integer> getMinions(ArrayList<String> ename) throws Exception {
		ResultSet r = db.select("employeeID, first_name, last_name", "employeeInfo", "supervisorID = " + employeeID ,"last_name", "ASC");
		
		ArrayList<Integer> eids = new ArrayList<Integer>();
		
		
		while(r.next()) {
			eids.add(r.getInt("employeeID"));
			ename.add(r.getString("last_name") + ", " + r.getString("first_name"));
		}
		return eids;
	}
	
	public void logHours(DateTime start, DateTime end, int employeeID) throws Exception {

		DateTime today = new DateTime();
		
		double hours = 0;
		Duration duration = new Duration(start, end);
		hours = duration.getStandardHours() + (duration.getStandardMinutes()-duration.getStandardHours()*60) / 60;
		
		DateTimeFormatter formatter = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss");
		
		//db.updateInfo("INSERT INTO alerts (description, requestDate, startDate, endDate, employeeID, supervisorID) VALUES ('Request Time Off', '" + today.toString(dft) + "', '" + start.toString(df) + "', '" + end.toString(df) + "', " + employeeID + ", " + this.getSupervisorID() + " )");
		//System.out.println(start.toString(dft));
		db.insert("hours", "dateEntered, hoursWorked, startTime, endTime, employeeID", "'" + today.toString(formatter) + "', " + hours + ", '" + start.toString(formatter) + "', '" + end.toString(dft) + "', " + employeeID);
	}

	public void logRange(DateTime rangeStart, DateTime rangeEnd,
			Vector<Log> ok, int selectedEmp) {
		// TODO Auto-generated method stub
		db.delete("hours", "DATE(startTime) BETWEEN '" + rangeStart.toString(dft) + "' AND '" + rangeEnd.toString(dft) +"' AND employeeID = " + employeeID);
		try {
			for(int i = 0; i < ok.size(); ++i)
				logHours(ok.get(i).startTime, ok.get(i).endTime, selectedEmp);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
	}

	public Vector<Integer> getAllJobs(Vector<String> jobNames, Vector<Double> minPay, Vector<Double> maxPay) {
		
		Vector<Integer> jobIDs = null;
		
		try {
			ResultSet r = db.select("*", "pay", "");
			
			jobIDs = new Vector<Integer>(); 
			
			while(r.next()) {
				jobIDs.add(r.getInt("jobID"));
				jobNames.add(r.getString("jobName"));
				minPay.add(r.getDouble("minPay"));
				maxPay.add(r.getDouble("maxPay"));
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return jobIDs;
	}
}
